package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import entity.Product;
import utils.PageUtils;

public class ProductDao extends BaseDao{
	public Connection conn;
	public PreparedStatement pstmt;
	public ResultSet rs;
	//分页显示列表
	public List<Product> proList(int categoryId,int categoryLeve,int startIndex,int pageSize){
		List<Product> list=new ArrayList<Product>();
		try {
			conn=super.getConnection();

			String sql="SELECT * from  easybuy_product where isDelete= 0 ";
			switch (categoryLeve) {
			case 1:
				sql +="and categoryLevel1=?";
				break;
			case 2:
				sql +="and categoryLevel2=?";
				break;
			case 3:
				sql +="and categoryLevel3=?";
				break;
			default:
				break;
			}
			sql +=" LIMIT ?,?";

			pstmt=conn.prepareStatement(sql);

			pstmt.setInt(1, categoryId);

			pstmt.setObject(2, startIndex);

			pstmt.setObject(3, pageSize);

			rs=pstmt.executeQuery();

			while (rs.next()) {
				Product pro=new Product();
				pro.setId(rs.getInt("id"));
				pro.setName(rs.getString("name"));
				pro.setIsDelete(rs.getInt("isDelete"));
				pro.setStock(rs.getInt("stock"));
				pro.setCategoryLeve1(rs.getInt("categoryLevel1"));
				pro.setCategoryLeve2(rs.getInt("categoryLevel2"));
				pro.setCategoryLeve3(rs.getInt("categoryLevel3"));
				pro.setDescription(rs.getString("description"));
				pro.setFileName(rs.getString("fileName"));
				pro.setPrice(rs.getFloat("price"));
				list.add(pro);
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally {
			super.closeAll(conn, pstmt, rs);
		}
		return list;
	}
	//总页数
	public int procount(int categoryId,int categoryLeve){
		int count=0;
		try {
			conn=super.getConnection();

			String sql="SELECT count(*) from  easybuy_product where isDelete= 0 ";
			switch (categoryLeve) {
			case 1:
				sql +="and categoryLevel1=?";
				break;
			case 2:
				sql +="and categoryLevel2=?";
				break;
			case 3:
				sql +="and categoryLevel3=?";
				break;
			default:
				break;
			}

			pstmt=conn.prepareStatement(sql);

			pstmt.setInt(1, categoryId);

			rs=pstmt.executeQuery();

			if (rs.next()) {
				count=rs.getInt("count(*)");
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally {
			super.closeAll(conn, pstmt, rs);
		}
		return count;
	}
	//根据id查找商品详情
	public Product findById(int id) {
		Product product =null;
		try {
			conn=super.getConnection();

			String sql="SELECT * from  easybuy_product where id=? ";

			pstmt=conn.prepareStatement(sql);

			pstmt.setInt(1, id);

			rs=pstmt.executeQuery();

			while (rs.next()) {
				product=new Product();
				product.setId(rs.getInt("id"));
				product.setName(rs.getString("name"));
				product.setIsDelete(rs.getInt("isDelete"));
				product.setStock(rs.getInt("stock"));
				product.setCategoryLeve1(rs.getInt("categoryLevel1"));
				product.setCategoryLeve2(rs.getInt("categoryLevel2"));
				product.setCategoryLeve3(rs.getInt("categoryLevel3"));
				product.setDescription(rs.getString("description"));
				product.setFileName(rs.getString("fileName"));
				product.setPrice(rs.getFloat("price"));
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally {
			super.closeAll(conn, pstmt, rs);
		}
		return product;
	}
	
    public int subStock(int productId,int quantity){
		int result=0;
    	try {
    		
    		conn = super.getConnection();
    		
    		String sql=" update easybuy_product set stock=stock-? where id=?  ";
    		
    		pstmt = conn.prepareStatement(sql);
    		
    		pstmt.setObject(1, quantity);
    		pstmt.setObject(2, productId);

    		result = pstmt.executeUpdate();
            
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
        	super.closeAll(conn, pstmt, rs);
        }
    	return result;
	}

	public List<Product> findList(int startIndex, int pageSize){
		List<Product> list = new ArrayList<>();
		try {
			conn = super.getConnection();
			String sql = "select id,name,description,price,stock,categoryLevel1,categoryLevel2,categoryLevel3,fileName,isDelete "
					+ "from easybuy_product where isDelete=0 " ;
			sql += " limit ?,?";
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setObject(1, startIndex);
			pstmt.setObject(2, pageSize);
			rs = pstmt.executeQuery();
			
			while (rs.next()) {
				Product product = new Product();
		        product.setId(rs.getInt("id"));
		        product.setName(rs.getString("name"));
		        product.setDescription(rs.getString("description"));
		        product.setPrice(rs.getFloat("price"));
		        product.setStock(rs.getInt("stock"));
		        product.setCategoryLeve1(rs.getInt("categoryLevel1"));
		        product.setCategoryLeve2(rs.getInt("categoryLevel2"));
		        product.setCategoryLeve3(rs.getInt("categoryLevel3"));
		        product.setFileName(rs.getString("fileName"));
				list.add(product);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
        	super.closeAll(conn, pstmt, rs);
        }
		return list;
	}

	public int findCount(){
		int count=0;
		try {
			conn = super.getConnection();
			String sql = "select count(1) "
					+ "from easybuy_product where isDelete=0 " ;
			
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			
			if (rs.next()) {
				count = rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
        	super.closeAll(conn, pstmt, rs);
        }
		
		return count;
	}
	public PageUtils<Product> findProductsByCategoryId(int categoryId,int categoryLevel,int beginIndex,int pageSize) {
		PageUtils<Product> pageUtil = new PageUtils<>();
		pageUtil.setPageSize(pageSize);
		List<Product> list = new ArrayList<>();
		try {
			conn = super.getConnection();
			
			//查询内容数据
			String sql = "select id,name,description,price,stock,categoryLevel1,categoryLevel2,categoryLevel3,fileName,isDelete "
					+ " from easybuy_product where isDelete=0 ";
			
			switch (categoryLevel) {
			case 1:
				sql += "and categoryLevel1Id=? ";
				break;
			case 2:
				sql += "and categoryLevel2Id=? ";
				break;
			case 3:
				sql += "and categoryLevel3Id=? ";
				break;
			}
			
			sql += "limit ?,?";
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setObject(1, categoryId);
			pstmt.setObject(2, beginIndex);
			pstmt.setObject(3, pageSize);
			rs = pstmt.executeQuery();
			
			while (rs.next()) {
				Product product = new Product();
		        product.setId(rs.getInt("id"));
		        product.setName(rs.getString("name"));
		        product.setDescription(rs.getString("description"));
		        product.setPrice(rs.getFloat("price"));
		        product.setStock(rs.getInt("stock"));
		        product.setCategoryLeve1(rs.getInt("categoryLevel1"));
		        product.setCategoryLeve2(rs.getInt("categoryLevel2"));
		        product.setCategoryLeve3(rs.getInt("categoryLevel3"));
		        product.setFileName(rs.getString("fileName"));
				list.add(product);
			}
			//将集合数据存到pageUtil里
			pageUtil.setList(list);
			
			int totalCount = 0;
			//查询记录数
			String sqlCount = "select count(1) "
					+ " from easybuy_product where isDelete=0 ";
			
			switch (categoryLevel) {
			case 1:
				sqlCount += "and categoryLevel1=? ";
				break;
			case 2:
				sqlCount += "and categoryLevel2=? ";
				break;
			case 3:
				sqlCount += "and categoryLevel3=? ";
				break;
			}
			
			pstmt = conn.prepareStatement(sqlCount);
			pstmt.setObject(1, categoryId);
			rs = pstmt.executeQuery();
			
			if (rs.next()) {
				totalCount = rs.getInt(1);
			}
			pageUtil.setTotalCount(totalCount);
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
        	super.closeAll(conn, pstmt, rs);
        }
		return pageUtil;
	}

	public Product getProductById(int id) {
		Product product = null;
		try {
			conn = super.getConnection();
			
			//查询内容数据
			String sql = "select id,name,description,price,stock,categoryLevel1,categoryLevel2,categoryLevel3,fileName,isDelete  "
					+ " from easybuy_product where id=? ";
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setObject(1, id);
			rs = pstmt.executeQuery();
			
			if (rs.next()) {
				product = new Product();
		        product.setId(rs.getInt("id"));
		        product.setName(rs.getString("name"));
		        product.setDescription(rs.getString("description"));
		        product.setPrice(rs.getFloat("price"));
		        product.setStock(rs.getInt("stock"));
		        product.setCategoryLeve1(rs.getInt("categoryLevel1"));
		        product.setCategoryLeve2(rs.getInt("categoryLevel2"));
		        product.setCategoryLeve3(rs.getInt("categoryLevel3"));
		        product.setFileName(rs.getString("fileName"));
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
        	super.closeAll(conn, pstmt, rs);
        }
		
		return product;
	}
	public PageUtils<Product> findAllProducts(int beginIndex, int pageSize) {
		PageUtils<Product> pageUtil = new PageUtils<>();
		pageUtil.setPageSize(pageSize);
		List<Product> list = new ArrayList<>();
		try {
			conn = super.getConnection();
			
			//查询内容数据
			String sql = "select id,name,description,price,stock,categoryLevel1,categoryLevel2,categoryLevel3,fileName,isDelete "
					+ " from easybuy_product where isDelete=0 ";
			
			sql += "limit ?,?";
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setObject(1, beginIndex);
			pstmt.setObject(2, pageSize);
			rs = pstmt.executeQuery();
			
			while (rs.next()) {
				Product product = new Product();
		        product.setId(rs.getInt("id"));
		        product.setName(rs.getString("name"));
		        product.setDescription(rs.getString("description"));
		        product.setPrice(rs.getFloat("price"));
		        product.setStock(rs.getInt("stock"));
		        product.setCategoryLeve1(rs.getInt("categoryLevel1"));
		        product.setCategoryLeve2(rs.getInt("categoryLevel2"));
		        product.setCategoryLeve3(rs.getInt("categoryLevel3"));
		        product.setFileName(rs.getString("fileName"));
				list.add(product);
			}
			//将集合数据存到pageUtil里
			pageUtil.setList(list);
			
			int totalCount = 0;
			//查询记录数
			String sqlCount = "select count(1) "
					+ " from easybuy_product where isDelete=0 ";
			
			pstmt = conn.prepareStatement(sqlCount);
			rs = pstmt.executeQuery();
			
			if (rs.next()) {
				totalCount = rs.getInt(1);
			}
			pageUtil.setTotalCount(totalCount);
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
        	super.closeAll(conn, pstmt, rs);
        }
		return pageUtil;
	}

	public int add(Product product) {
		int result=0;
    	try {
    		
    		conn = super.getConnection();
    		
    		String sql="insert into easybuy_product(name,description,price,stock,categoryLevel1,categoryLevel2,categoryLevel3,fileName,isDelete) values(?,?,?,?,?,?,?,?,?) ";
    		
    		pstmt = conn.prepareStatement(sql);
    		
    		pstmt.setObject(1, product.getName());
    		pstmt.setObject(2, product.getDescription());
    		pstmt.setObject(3, product.getPrice());
    		pstmt.setObject(4, product.getStock());
    		pstmt.setObject(5, product.getCategoryLeve1());
    		pstmt.setObject(6, product.getCategoryLeve2());
    		pstmt.setObject(7, product.getCategoryLeve3());
    		pstmt.setObject(8, product.getFileName());
    		pstmt.setObject(9, 0);

    		result = pstmt.executeUpdate();
            
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
        	super.closeAll(conn, pstmt, rs);
        }
    	return result;
	}

	public int update(Product product) {
		int result=0;
    	try {
    		
    		conn = super.getConnection();
    		
    		String sql=" update easybuy_product set name=?,description=?,price=?,stock=?,categoryLevel1=?,categoryLevel2=?,categoryLevel3=?,fileName=?,isDelete=? where id=?  ";
    		
    		pstmt = conn.prepareStatement(sql);
    		
    		pstmt.setObject(1, product.getName());
    		pstmt.setObject(2, product.getDescription());
    		pstmt.setObject(3, product.getPrice());
    		pstmt.setObject(4, product.getStock());
    		pstmt.setObject(5, product.getCategoryLeve1());
    		pstmt.setObject(6, product.getCategoryLeve2());
    		pstmt.setObject(7, product.getCategoryLeve3());
    		pstmt.setObject(8, product.getFileName());
    		pstmt.setObject(9, 0);
    		
    		pstmt.setObject(10, product.getId());

    		result = pstmt.executeUpdate();
            
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
        	super.closeAll(conn, pstmt, rs);
        }
    	return result;
	}

    public int deteleProduct(int id){
    	int result2=0;
    	try {
    		
    		conn = super.getConnection();
    		
    		String sql=" DELETE from easybuy_product where id = ? ";
    		
    		pstmt = conn.prepareStatement(sql);
    		
    		pstmt.setObject(1, id);

    		result2 = pstmt.executeUpdate();
            
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
        	super.closeAll(conn, pstmt, rs);
        }
    	return result2;
    }
}
